﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public class EventInfoDAL
    {
        public static readonly SqlConnectionStringBuilder DispatchBuilder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);//取调度库的链接字符串
        public static readonly string DispatchDatabase = DispatchBuilder.InitialCatalog;
        #region
        /// <summary>
        /// 事件信息列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="mainSuit"></param>
        /// <param name="telephoneNumber"></param>
        /// <param name="localAddress"></param>
        /// <param name="patientName"></param>
        /// <param name="sendAddress"></param>
        /// <param name="dispatcher"></param>
        /// <param name="driver"></param>
        /// <param name="doctor"></param>
        /// <param name="nurse"></param>
        /// <param name="eventType"></param>
        /// <param name="illnessJudgment"></param>
        /// <param name="eventCode"></param>
        /// <param name="station"></param>
        /// <param name="ambulanceCode"></param>
        /// <param name="eventSource"></param>
        /// <returns></returns>
        public object GetEventInfoList(int pageSize, int pageIndex, DateTime start, DateTime end, string contactTel, string telephoneNumber, string localAddress,
                                       string patientName, string sendAddress, string dispatcher, string eventType, string center, string station,
                                       string ambulanceCode, E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID, string eventDispatchCenterID)
        {
            List<EventInfo> list = new List<EventInfo>();
            StringBuilder sb = new StringBuilder();
sb.Append(@"
select distinct tae.事件编码
into #temp1
from TAlarmEvent tae 
left join TAcceptEvent tac on tac.事件编码=tae.事件编码 
left join TTask tt on tac.事件编码 = tt.事件编码 and tac.受理序号=tt.受理序号
where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("tae.首次受理时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("tae.首次受理时刻", end, sb);
//            sb.Append(@" 
//and tae.是否测试=0 ");
            WhereClauseUtility.AddStringLike("tac.联系电话", contactTel, sb);
            WhereClauseUtility.AddStringLike("tac.呼救电话", telephoneNumber, sb);
            WhereClauseUtility.AddStringLike("tac.现场地址", localAddress, sb);
            WhereClauseUtility.AddStringLike("tac.患者姓名", patientName, sb);
            WhereClauseUtility.AddStringLike("tac.送往地点", sendAddress, sb);
            WhereClauseUtility.AddStringLike("tac.责任受理人编码", dispatcher, sb);
            WhereClauseUtility.AddStringEqual("tae.事件类型编码", eventType, sb);
            WhereClauseUtility.AddInSelectQuery("tt.车辆编码", ambulanceCode, sb);
            //2020-11-22 zch 上海120 中心与郊县权限判断 1-6 为中心， 其他为郊县 所有的事件都需要这个权限
            WhereClauseUtility.AddStringEqual("tae.中心编码", eventDispatchCenterID, sb);

            sb.Append(@"
select distinct identity(int,1,1) as 行号
,呼救电话=tac.呼救电话,现场地址=tac.现场地址,送往地点=tac.送往地点
--,所派车辆=case when 执行任务总数=0 then NULL else dbo.GetEventAmbs(tae.事件编码) end
,患者姓名=tac.患者姓名,主诉=tac.主诉
,首次受理时刻=tae.首次受理时刻,调度员=tp.姓名,受理次数=tae.受理次数			 
,呼救时刻=tac.电话振铃时刻,事件编码=tae.事件编码
into  #tempa           
from TAlarmEvent tae 
left join TPerson tp on tp.编码 = tae.首次调度员编码                    
--left join TZAlarmEventType tzaet on tae.事件类型编码 = tzaet.编码
left join TAcceptEvent tac on tac.事件编码=tae.事件编码 				              
left join TTask tt on tac.事件编码 = tt.事件编码 and tac.受理序号=tt.受理序号
--left join TAmbulance ta on ta.任务编码 = tt.任务编码 
left join TTaskPersonLink ttpl on ttpl.任务编码 = tt.任务编码
left join TPerson tp1 on tp1.编码 = ttpl.人员编码					
left join TStation ts on ts.编码 = tt.分站编码
--left join TCenter tc on tc.编码 = ts.中心编码                                       
where tae.事件编码 in(select 事件编码 from #temp1) and tac.受理序号=1 ");

            //2020-11-22 zch 上海120 中心与郊县权限判断 1-6 为中心， 其他为郊县 所有的事件都需要这个权限
            WhereClauseUtility.AddStringEqual("tae.中心编码", eventDispatchCenterID, sb);
           
            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddInSelectQuery("ts.中心编码", center, sb);
                    WhereClauseUtility.AddInSelectQuery("ts.编码", station, sb);
                }

                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("ts.中心编码", selfCenterID, sb);
                    WhereClauseUtility.AddInSelectQuery("ts.编码", station, sb);
                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    WhereClauseUtility.AddStringEqual("ts.编码", selfStationID, sb);
                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    sb.Append("and tp1.工号 = '" + selfWorkCode + @"' ");
                }

                sb.Append(@" 
order by 首次受理时刻 desc 
select * into #temp from #tempa where 1=1 ");
                sb.Append(@"
select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
                sb.Append(@"
SELECT count(*) FROM #temp t
drop table #temp1,#tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        EventInfo info = new EventInfo();
                        info.callPhone = dr["呼救电话"].ToString();
                        info.localAdr = dr["现场地址"].ToString();
                        info.sendAdr = dr["送往地点"].ToString();
                        //info.dispatchedCar = dr["所派车辆"].ToString();
                        info.pName = dr["患者姓名"].ToString();
                        info.mainSuit = dr["主诉"].ToString();
                        info.firstAcceptTime = dr["首次受理时刻"].ToString();
                        info.firstDispatcher = dr["调度员"].ToString();
                        info.acceptTimes = Convert.ToInt32(dr["受理次数"]);
                        info.callTime = dr["呼救时刻"].ToString();
                        info.EventCode = dr["事件编码"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
        }

        #endregion

        #region 获取combobox的值
        //获取事件类型
        public object GetEventTypeList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码,名称 from TZAlarmEventType where 是否有效 = 1");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["名称"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取事件来源
        public object GetEventSourceList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码,名称 from TZAlarmEventOrigin where 是否有效 = 1");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["名称"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        //获取病情判断
        public object GetIllnessStateList()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码,名称 from TZIllState where 是否有效 = 1");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["名称"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        //获取车辆分站
        public object GetStationList(string eventDispatchCenterID)
        {
            List<P_Role> list = new List<P_Role>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码,名称 from TStation ts where 是否有效 = 1");
            if (eventDispatchCenterID == "1")
                sb.Append(" and ts.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ts.中心编码", eventDispatchCenterID, sb); //2020-11-22 zch
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_Role info = new P_Role();
                    info.ID = Convert.ToInt32(dr["编码"]);
                    info.Name = dr["名称"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        //获取实际标识
        public object GetAmbulanceCodeList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            //sb.Append(@"select 车辆编码,实际标识 from TAmbulance where 是否有效 = 1 ");
            sb.Append(@"select 车辆编码,实际标识 from TAmbulance ta left join TStation ts on ta.分站编码= ts.编码 where ta.是否有效 = 1 ");
            if (eventDispatchCenterID == "1")
                sb.Append(" and ts.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ts.中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["车辆编码"].ToString();
                    info.Name = dr["实际标识"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取调度员
        public object GetDispatcherList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            //sb.Append(@"select 编码,姓名 from TPerson where 是否有效 = 1 and 类型编码 = 2  ");
            sb.Append(@"select tp.编码,姓名 from TPerson tp
                        left join TSTation ts on tp.分站编码=ts.编码
                        where tp.是否有效 = 1 and tp.类型编码 in (2,9)   ");
            if (eventDispatchCenterID == "1")
                sb.Append(" and ts.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ts.中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["姓名"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取司机
        public object GetDriverList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            //sb.Append(@"select 编码,姓名 from TPerson where 是否有效 = 1 and 类型编码 = 3  ");
            sb.Append(@"select tp.编码,tp.姓名,tp.工号 from TPerson tp
                        left join TSTation ts on tp.分站编码=ts.编码
                        where tp.是否有效 = 1 and tp.类型编码 = 3 ");
            if (eventDispatchCenterID == "1")
                sb.Append(" and ts.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ts.中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["姓名"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取医生
        public object GetDoctorList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            //sb.Append(@"select 编码,姓名 from TPerson where 是否有效 = 1 and 类型编码 = 4  ");
            sb.Append(@"select tp.编码,tp.姓名,tp.工号 from TPerson tp
                        left join TSTation ts on tp.分站编码=ts.编码
                        where tp.是否有效 = 1 and tp.类型编码 = 4 ");
            if (eventDispatchCenterID == "1")
                sb.Append(" and ts.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ts.中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["姓名"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取护士
        public object GetNurseList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            //sb.Append(@"select 编码,姓名 from TPerson where 是否有效 = 1 and 类型编码 = 5  ");
            sb.Append(@"select tp.编码,tp.姓名,tp.工号 from TPerson tp
                        left join TSTation ts on tp.分站编码=ts.编码
                        where tp.是否有效 = 1 and tp.类型编码 = 5 ");
            if (eventDispatchCenterID == "1")
                sb.Append(" and ts.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ts.中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["姓名"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取担架员
        public object GetStretcherList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            //sb.Append(@"select 编码,姓名 from TPerson where 是否有效 = 1 and 类型编码 = 6  ");
            sb.Append(@"select tp.编码,tp.姓名,tp.工号 from TPerson tp
                        left join TSTation ts on tp.分站编码=ts.编码
                        where tp.是否有效 = 1 and tp.类型编码 = 6 ");
            if (eventDispatchCenterID == "1")
                sb.Append(" and ts.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("ts.中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["编码"].ToString();
                    info.Name = dr["姓名"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        #endregion
    }
}
